﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace App_1Mark1
{
    public partial class frm_G_Eleve : Form
    {
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        SqlCommand sqlCom = new SqlCommand();
        SqlConnection sqlC = new SqlConnection();
        SqlCommandBuilder sqlCB = new SqlCommandBuilder();
        DataSet DS = new DataSet();

        public frm_G_Eleve()
        {
            InitializeComponent();
        }

        private void frm_G_Eleve_Load(object sender, EventArgs e)
        {
            sqlC.ConnectionString = "Data Source=DESKTOP-03S1896;Initial Catalog=Gestion_Scolarité;Integrated Security=True";
            sqlC.Open();
            sqlCom.Connection = sqlC;
            fillGrid();
        }

        void fillGrid()
        {
            sqlCom.CommandText = "select CNE, Nom_ET, Prenom_ET, g.NUM_Groupe, Nom_Filiere from Etudiant e inner join Groupe g on g.NUM_Groupe=e.NUM_Groupe inner join Filiere f on f.ID_Filiere=g.ID_Filiere";
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Etudiant");
            dgvEleve.DataSource = DS.Tables["Etudiant"];
            dgvEleve.Columns[1].HeaderText = "Nom";
            dgvEleve.Columns[2].HeaderText = "Prenom";
            dgvEleve.Columns[3].HeaderText = "Nº Groupe";
            dgvEleve.Columns[4].HeaderText = "Filiere";
            dgvEleve.Columns[3].Width = 101;
        }

        void fillComboB()
        {
            sqlCom.CommandText = "select * from Groupe";
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Groupe");
            cbClass.DataSource = DS.Tables["Groupe"];
            cbClass.DisplayMember = "NUM_Groupe";
            cbClass.ValueMember = "NUM_Groupe";
        }

        private void rdClass_CheckedChanged(object sender, EventArgs e)
        {
            cbClass.SelectedIndexChanged -= cbClass_SelectedIndexChanged;
            fillComboB();
            DS.Tables["Etudiant"].Clear();
            sqlCom.CommandText = "select CNE, Nom_ET, Prenom_ET, g.NUM_Groupe, Nom_Filiere from Etudiant e inner join Groupe g on g.NUM_Groupe=e.NUM_Groupe inner join Filiere f on f.ID_Filiere=g.ID_Filiere where G.NUM_Groupe = " + cbClass.SelectedValue;
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Etudiant");
            dgvEleve.DataSource = DS.Tables["Etudiant"];
            cbClass.SelectedIndexChanged += cbClass_SelectedIndexChanged;
            cbClass.Visible = true;
        }

        private void cbClass_SelectedIndexChanged(object sender, EventArgs e)
        {
            DS.Tables["Etudiant"].Clear();
            sqlCom.CommandText = "select CNE, Nom_ET, Prenom_ET, g.NUM_Groupe, Nom_Filiere from Etudiant e inner join Groupe g on g.NUM_Groupe=e.NUM_Groupe inner join Filiere f on f.ID_Filiere=g.ID_Filiere where G.NUM_Groupe = " + cbClass.SelectedValue;
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Etudiant");
            dgvEleve.DataSource = DS.Tables["Etudiant"];
        }

        private void rdAll_CheckedChanged(object sender, EventArgs e)
        {
            cbClass.SelectedIndexChanged -= cbClass_SelectedIndexChanged;
            cbClass.Visible = false;
            DS.Tables["Etudiant"].Clear();
            try
            {
                DS.Tables["Groupe"].Clear();
            }
            catch (Exception ex) { ex.ToString(); }
            fillGrid();
            cbClass.SelectedIndexChanged += cbClass_SelectedIndexChanged;
        }
    }
}
